#!/usr/bin/python3
# -*- coding: utf-8 -*-

"""
# @version: v1.0
# @author : wlis
# @Email : 1259319710@qq.com
# @Project : G-Carbon Bio
# @File : mysql_help.py
# @Software: PyCharm
# @time: 2025/1/25 13:26
# @description : MySql 帮助类
"""
import pymysql
import os
from service.mysql_config import DATABASE_CONFIG  # 导入数据库配置


class MySQLHelper:
    def __init__(self):
        """
        MySQL数据库操作助手类，使用DATABASE_CONFIG进行连接
        """
        # 切换到脚本所在目录的父目录
        os.chdir(os.path.dirname(os.path.dirname(__file__)))

        # 从DATABASE_CONFIG中获取连接信息
        host = DATABASE_CONFIG['HOST']
        port = DATABASE_CONFIG['PORT']
        username = DATABASE_CONFIG['USERNAME']
        password = DATABASE_CONFIG['PASSWORD']
        database = DATABASE_CONFIG['DATABASE']

        self.conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
        self.cursor = self.conn.cursor()

    def execute_query(self, sql, params=None):
        """
        执行查询语句

        Args:
            sql (str): 查询语句
            params (tuple): 查询参数

        Returns:
            list: 查询结果
        """
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result

    def execute_batch_insert(self, table, columns, values_list):
        """
        执行批量插入语句

        Args:
            table (str): 表名
            columns (str): 列名，多个列用逗号分隔
            values_list (list): 值列表，每个值为一个元组，对应一行数据
        """
        placeholders = ', '.join(['%s'] * len(columns.split(',')))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        self.cursor.executemany(sql, values_list)
        self.conn.commit()

    def execute_update(self, sql, params=None):
        """
        执行更新语句

        Args:
            sql (str): 更新语句
            params (tuple): 更新参数
        """
        self.cursor.execute(sql, params)
        self.conn.commit()

    def execute_update_batch(self, sql_statements, params_list):
        """
        执行批量更新语句
        """
        for sql, params in zip(sql_statements, params_list):
            try:
                self.cursor.execute(sql, params)
                self.conn.commit()
            except Exception as err:
                print(f"Error executing SQL: {sql} with params {params}. Error: {err}")
                self.conn.rollback()

    def execute_insert(self, table, columns, values):
        """
        执行插入语句

        Args:
            table (str): 表名
            columns (str): 列名，多个列用逗号分隔
            values (str): 值，多个值用逗号分隔
        """
        sql = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        self.execute_update(sql)

    def execute_delete(self, table, condition):
        """
        执行删除语句

        Args:
            table (str): 表名
            condition (str): 删除条件
        """
        sql = f"DELETE FROM {table} WHERE {condition}"
        self.execute_update(sql)

    def execute_update_data(self, table, set_value, condition):
        """
        执行更新数据语句

        Args:
            table (str): 表名
            set_value (str): 设置的值
            condition (str): 更新条件
        """
        sql = f"UPDATE {table} SET {set_value} WHERE {condition}"
        self.execute_update(sql)



    def close(self):
        """关闭数据库连接"""
        self.cursor.close()
        self.conn.close()
